/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.comut.crud.dao;

import com.comut.crud.pojo.FindAndSet;
import com.comut.lib.error.ErrorCodes;
import com.comut.lib.exception.BaseException;
import com.comut.lib.exception.ObjectFoundException;
import com.comut.lib.exception.ObjectNotFoundException;
import com.comut.crud.pojo.subgroup.Subgroup;
import java.sql.*;
import org.apache.log4j.Logger;

/**
 *
 * @author asantos
 */
public class SubgroupDao {
    

    private static Logger _logger = Logger.getLogger(SubgroupDao.class.getName());
    private Connection _connection;

    public SubgroupDao(Connection connection) {
        this._connection = connection;
    }

    public ResultSet getSubgroup(int id) {

        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getSubgroup](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, id);
            rs = stmt.executeQuery();
        } catch (SQLException ex) {
            _logger.error("getSubgroup error", ex);
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
        }

        return rs;
    }

    public int createSubgroup(Subgroup subgroup) {

        int toRet = -1;
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_createSubgroup](?,?,?,?,?)}";
        try {

            stmt = _connection.prepareCall(query);
            stmt.setInt(1, subgroup.getId_group());
            stmt.setInt(2, subgroup.getId_owner());
            stmt.setString(3, subgroup.getAlias());            
            stmt.setString(4, subgroup.getDesc());
            stmt.setString(5, subgroup.getId_logo());
            rs = stmt.executeQuery();

            if (rs.next()) {
                toRet = rs.getInt(1);
            }
        } catch (SQLException ex) {
            _logger.error("createSubgroup error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } catch (Exception ex) {
            _logger.error("createSubgroup error", ex);
            throw new BaseException(ex, ErrorCodes.GENERAL_ERROR);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

        return toRet;
    }

    /**
     * Update the subgroup table in SQLServer
     *
     * @param subgroup Object with the where clause and the update clause
     */
    public void updateSubgroup(FindAndSet subgroup) {

        String update = "UPDATE [auth].[struct_sub_group]  SET "+subgroup.getUpdate();
        String where = " WHERE "+subgroup.getFind();

        PreparedStatement stmt = null;
        try {
            stmt = _connection.prepareStatement(update + where);
            int updateRows = stmt.executeUpdate();
            if (updateRows == 0) {
                throw new ObjectNotFoundException("The subgroup does not exist", ErrorCodes.OBJECT_NOT_FOUND);
            }
        } catch (SQLException ex) {
            _logger.fatal("updateSubgroup error", ex);
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

    }

    public void deleteSubgroup(int id) {
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_deleteSubgroup](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, id);
            stmt.execute();
        } catch (SQLException ex) {
            _logger.error("deleteSubgroup error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_NOT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    
}
